package com.authstr.support;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/*
   •要执行SQL语句，必须获得java.sql.Statement实例，Statement实例分为以下3 种类型：
     1、执行静态SQL语句。通常通过Statement实例实现。
     2、执行动态SQL语句。通常通过PreparedStatement实例实现。
     3、执行数据库存储过程。通常通过CallableStatement实例实现。
   •具体的实现方式：
      Statement stmt = con.createStatement() ;
      PreparedStatement pstmt = con.prepareStatement(sql) ;
      CallableStatement cstmt = con.prepareCall("{CALL demoSp(? , ?)}") ;
*/
public class JdbcConnectionUtil {

    public static final String ENCODING_UTF8="?useSSL=false&useUnicode=true&characterEncoding=utf-8";//编码格式
    public static final String ENCODING_GBK="?useUnicode=true&characterEncoding=gbk";//编码格式
    public static final String DATABASE_DRIVER_MYSQL="com.mysql.jdbc.Driver";//MYSQL 数据库驱动
    private String username;//用户名
    private String password;//密码
    private String link_url;//数据库链接地址
    private String database_driver;//数据库驱动

    private Connection coon;//数据库连接
    private Statement stmt;//执行静态SQL对象
    private PreparedStatement pstmt;//预编译SQL对象

    public JdbcConnectionUtil(String username, String password, String link_url) throws SQLException, ClassNotFoundException {
        this(username,password,link_url,DATABASE_DRIVER_MYSQL);
    }

    public JdbcConnectionUtil(String username, String password, String link_url, String database_driver) throws SQLException, ClassNotFoundException {
        this.setMainParameter(username,password,link_url,database_driver);
        this.getConnection();
    }


    /**
     * 设置或者修改核心数据库连接参数
     * @param username
     * @param password
     * @param link_url
     * @param database_driver
     */
    public void setMainParameter(String username, String password, String link_url, String database_driver){
        this.username = username;
        this.password = password;
        this.link_url = link_url;
        this.database_driver = database_driver;
    }

    /**
     * 获取数据库连接,到当前对象
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void getConnection() throws ClassNotFoundException, SQLException {
        Class.forName(this.getDatabase_driver());
        this.coon = DriverManager.getConnection(this.getLink_url() , this.getUsername() , this.getPassword()) ;
    }

    /**
     * 获取Statement对象,用来进行静态查询
     * @throws SQLException
     */
    public void getStatement() throws SQLException {
        this.stmt = coon.createStatement();
    }

    /**
     * 获取preparedStatement对象,用来进行带参数查询
     * @param sql
     */
    public void getPreparedStatement(String sql) throws SQLException {
        pstmt = coon.prepareStatement(sql);
    }



    /**
     * 查询结果转换,将ResultSet转换为List<Map>;类型
     * @param resultSet
     * @return
     * @throws SQLException
     */
    public List<Map<String,Object>> toListMap(ResultSet resultSet) throws SQLException {
        List<Map<String,Object>> result = new ArrayList<>();
        //按列获取
        ResultSetMetaData metaData = resultSet.getMetaData();
        int colCount = metaData.getColumnCount();
        Map<String,Object> map = null;
        while(resultSet.next()){
            map = new HashMap<>();
            for (int i = 1;i <= colCount;i++){
                String columnName = metaData.getColumnName(i);
                map.put(columnName,resultSet.getObject(columnName));
            }
            result.add(map);
        }
        return result;
    }

    /**
     *执行查询sql,获取查询结果
     * @param sql
     * @return
     */
    public ResultSet executeQuerySql(String sql) throws SQLException {
            getPreparedStatement(sql);
            return pstmt.executeQuery();
    }

    /**
     * 执行sql,获取List<Map>形式的结果
     * @param sql
     * @return
     */
    public List<Map<String,Object>> getQueryResult(String sql) throws SQLException {
        return toListMap(executeQuerySql(sql));
    }

    /**
     * 关闭所有连接
     */
    public void close() throws SQLException {
        if(null != stmt){
            stmt.close();
        }
        if(null != pstmt){
            pstmt.close();
        }
        if(null != coon){
            coon.close();
        }
    }



    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getLink_url() {
        return link_url;
    }

    public void setLink_url(String link_url) {
        this.link_url = link_url;
    }

    public String getDatabase_driver() {
        return database_driver;
    }

    public void setDatabase_driver(String database_driver) {
        this.database_driver = database_driver;
    }
}
